﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ISS.IR.RRNMS.Library.DataAccess
{
    public static partial class Main
    {
        public static BusinessLogic.Account GetAccountById(Guid AccountId)
        {
            BusinessLogic.Account tempAccount = new BusinessLogic.Account();

            try
            {
                command.CommandText = "usp_selectaccountbyid";
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("AccountId", AccountId));
                DataTable dtAccount = new DataTable();

                adapter.Fill(dtAccount);

                DataRow drAccount = dtAccount.Rows[0];

                tempAccount = FillAccount(drAccount);
            }
            catch (Exception exc)
            {

            }

            return tempAccount;
        }

        public static BusinessLogic.Account GetAccountByUNHCR(string UNHCR)
        {
            BusinessLogic.Account tempAccount = new BusinessLogic.Account();

            try
            {
                command.CommandText = "usp_selectaccountbyunhcr";
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("UNHCR", UNHCR));
                DataTable dtAccount = new DataTable();

                adapter.Fill(dtAccount);

                DataRow drAccount = dtAccount.Rows[0];

                tempAccount = FillAccount(drAccount);
            }
            catch (Exception exc)
            {

            }

            return tempAccount;
        }

        private static BusinessLogic.Account FillAccount(DataRow drAccount)
        {
            BusinessLogic.Account tempAccount = new BusinessLogic.Account();

            try
            {
                tempAccount.AccountId = Guid.Parse(drAccount["AccountId"].ToString());

                tempAccount.FirstName = drAccount["FirstName"].ToString();
                tempAccount.SecondName = drAccount["SecondName"].ToString();
                tempAccount.ThirdName = drAccount["ThirdName"].ToString();
                tempAccount.FourthName = drAccount["FourthName"].ToString();

                tempAccount.FullName = drAccount["FullName"].ToString();

                tempAccount.UNHCR = drAccount["UNHCR"].ToString();

                tempAccount.Gender = drAccount["GenderId"] == DBNull.Value ? new Item() : new Item()
                {
                    Id = Int32.Parse(drAccount["GenderId"].ToString()),
                    Value = drAccount["GenderName"].ToString()
                };

                tempAccount.Telephone = drAccount["Telephone"].ToString();
                tempAccount.Email = drAccount["Email"].ToString();
                tempAccount.AddressInEgypt = drAccount["AddressInEgypt"].ToString();

                tempAccount.Governorate = drAccount["GovernorateId"] == DBNull.Value ? new Item() : new Item()
                {
                    Id = Int32.Parse(drAccount["GovernorateId"].ToString()),
                    Value = drAccount["GovernorateName"].ToString()
                };

                tempAccount.Region = drAccount["RegionId"] == DBNull.Value ? new Item() : new Item()
                {
                    Id = Int32.Parse(drAccount["RegionId"].ToString()),
                    Value = drAccount["RegionName"].ToString()
                };

                tempAccount.BirthDate = drAccount["BirthDate"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(drAccount["BirthDate"]);
                tempAccount.BirthPlace = drAccount["BirthPlace"].ToString();

                tempAccount.MaritalStatus = drAccount["MaritalStatusId"] == DBNull.Value ? new Item() : new Item()
                {
                    Id = Int32.Parse(drAccount["MaritalStatusId"].ToString()),
                    Value = drAccount["MaritalStatusName"].ToString()
                };
                tempAccount.FamilyMembersCount = drAccount["FamilyMembersCount"] == DBNull.Value ? 0 : Int32.Parse(drAccount["FamilyMembersCount"].ToString());
                tempAccount.InfantsCount = drAccount["InfantsCount"] == DBNull.Value ? 0 : Int32.Parse(drAccount["InfantsCount"].ToString());

                tempAccount.IsInEmergency = drAccount["IsInEmergency"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsInEmergency"].ToString());
                tempAccount.EmergencyDescription = drAccount["EmergencyDescription"].ToString();

                tempAccount.WasDetainedInEgypt = drAccount["WasDetainedInEgypt"] == DBNull.Value ? false : Boolean.Parse(drAccount["WasDetainedInEgypt"].ToString());
                tempAccount.DetentionInEgyptDescription = drAccount["DetentionInEgyptDescription"].ToString();

                tempAccount.WasDetainedInHomeCountry = drAccount["WasDetainedInHomeCountry"] == DBNull.Value ? false : Boolean.Parse(drAccount["WasDetainedInHomeCountry"].ToString());
                tempAccount.DetentionInHomeCountryDescription = drAccount["DetentionInHomeCountryDescription"].ToString();

                tempAccount.AverageMonthlyIncome = drAccount["AverageMonthlyIncome"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["AverageMonthlyIncome"].ToString());
                tempAccount.OtherIncomeSource = drAccount["OtherIncomeSource"].ToString();
                tempAccount.LeaseAmount = drAccount["LeaseAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["LeaseAmount"].ToString());
                tempAccount.FoodAmount = drAccount["FoodAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["FoodAmount"].ToString());
                tempAccount.ElectricityAmount = drAccount["ElectricityAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["ElectricityAmount"].ToString());
                tempAccount.WaterAmount = drAccount["WaterAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["WaterAmount"].ToString());
                tempAccount.GasAmount = drAccount["GasAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["GasAmount"].ToString());
                tempAccount.EducationAmount = drAccount["EducationAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["EducationAmount"].ToString());
                tempAccount.MedicineAmount = drAccount["MedicineAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["MedicineAmount"].ToString());
                tempAccount.OtherAmount = drAccount["OtherAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["OtherAmount"].ToString());

                tempAccount.NumberOfRooms = drAccount["NumberOfRooms"] == DBNull.Value ? 0 : Int32.Parse(drAccount["NumberOfRooms"].ToString());
                tempAccount.HasElectricity = drAccount["HasElectricity"] == DBNull.Value ? false : Boolean.Parse(drAccount["HasElectricity"].ToString());
                tempAccount.HasSanitation = drAccount["HasSanitation"] == DBNull.Value ? false : Boolean.Parse(drAccount["HasSanitation"].ToString());
                tempAccount.HasFurniture = drAccount["HasFurniture"] == DBNull.Value ? false : Boolean.Parse(drAccount["HasFurniture"].ToString());
                tempAccount.HasDrinkingWater = drAccount["HasDrinkingWater"] == DBNull.Value ? false : Boolean.Parse(drAccount["HasDrinkingWater"].ToString());
                tempAccount.LivingStatus = drAccount["LivingStatusId"] == DBNull.Value ? new Item() : new Item()
                {
                    Id = Int32.Parse(drAccount["LivingStatusId"].ToString()),
                    Value = drAccount["LivingStatusName"].ToString()
                };

                tempAccount.JobDescriptionInHomeCountry = drAccount["JobDescriptionInHomeCountry"].ToString();
                tempAccount.TrainingCoursesWantedInEgypt = drAccount["TrainingCoursesWantedInEgypt"].ToString();
                tempAccount.JobWantedInEgypt = drAccount["JobWantedInEgypt"].ToString();

                tempAccount.ChronicDisease = drAccount["ChronicDiseaseId"] == DBNull.Value ? new Item() : new Item()
                {
                    Id = Int32.Parse(drAccount["ChronicDiseaseId"].ToString()),
                    Value = drAccount["ChronicDiseaseName"].ToString()
                };
                tempAccount.ChronicDiseaseOther = drAccount["ChronicDiseaseOther"].ToString();

                tempAccount.HasDisability = drAccount["HasDisability"] == DBNull.Value ? false : Boolean.Parse(drAccount["HasDisability"].ToString());
                tempAccount.IsDisabilityMedicalReportAttached = drAccount["IsDisabilityMedicalReportAttached"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsDisabilityMedicalReportAttached"].ToString());

                tempAccount.HasPsychiatricDisease = drAccount["HasPsychiatricDisease"] == DBNull.Value ? false : Boolean.Parse(drAccount["HasPsychiatricDisease"].ToString());
                tempAccount.IsPsychiatricDiseaseMedicalReportAttached = drAccount["IsPsychiatricDiseaseMedicalReportAttached"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsPsychiatricDiseaseMedicalReportAttached"].ToString());

                tempAccount.IsPregnant = drAccount["IsPregnant"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsPregnant"].ToString());
                tempAccount.IsPregnantMedicalReportAttached = drAccount["IsPregnantMedicalReportAttached"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsPregnantMedicalReportAttached"].ToString());

                tempAccount.HasPregnancyProblems = drAccount["HasPregnancyProblems"] == DBNull.Value ? false : Boolean.Parse(drAccount["HasPregnancyProblems"].ToString());
                tempAccount.IsPregnancyProblemsMedicalReportAttached = drAccount["IsPregnancyProblemsMedicalReportAttached"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsPregnancyProblemsMedicalReportAttached"].ToString());

                tempAccount.InterviewSummary = drAccount["InterviewSummary"].ToString();

                tempAccount.IsAction1 = drAccount["IsAction1"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction1"].ToString());
                tempAccount.Action1Description = drAccount["Action1Description"].ToString();

                tempAccount.IsAction2 = drAccount["IsAction2"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction2"].ToString());
                tempAccount.Action2Description = drAccount["Action2Description"].ToString();

                tempAccount.IsAction3 = drAccount["IsAction3"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction3"].ToString());
                tempAccount.Action3Description = drAccount["Action3Description"].ToString();

                tempAccount.IsAction4 = drAccount["IsAction4"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction4"].ToString());
                tempAccount.Action4Description = drAccount["Action4Description"].ToString();

                tempAccount.IsAction5 = drAccount["IsAction5"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction5"].ToString());
                tempAccount.Action5Description = drAccount["Action5Description"].ToString();

                tempAccount.IsAction6 = drAccount["IsAction6"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction6"].ToString());
                tempAccount.Action6Description = drAccount["Action6Description"].ToString();

                tempAccount.IsAction7 = drAccount["IsAction7"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction7"].ToString());
                tempAccount.Action7Description = drAccount["Action7Description"].ToString();

                tempAccount.IsAction8 = drAccount["IsAction8"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction8"].ToString());
                tempAccount.Action8Description = drAccount["Action8Description"].ToString();

                tempAccount.IsAction9 = drAccount["IsAction9"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction9"].ToString());
                tempAccount.Action9Description = drAccount["Action9Description"].ToString();

                tempAccount.IsAction10 = drAccount["IsAction10"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction10"].ToString());
                tempAccount.Action10Description = drAccount["Action10Description"].ToString();

                tempAccount.IsAction11 = drAccount["IsAction11"] == DBNull.Value ? false : Boolean.Parse(drAccount["IsAction11"].ToString());
                tempAccount.Action11Description = drAccount["Action11Description"].ToString();

                tempAccount.SocialWorkerDecisionCode = drAccount["SocialWorkerDecisionCode"].ToString();
                tempAccount.SocialWorkerDecisionDescription = drAccount["SocialWorkerDecisionDescription"].ToString();

                tempAccount.CommitteeDecision = drAccount["CommitteeDecision"].ToString();

                tempAccount.AidType = drAccount["AidTypeId"] == DBNull.Value ? new Item() : new Item()
                {
                    Id = Int32.Parse(drAccount["AidTypeId"].ToString()),
                    Value = drAccount["AidTypeName"].ToString()
                };
                tempAccount.AidAmount = drAccount["AidAmount"] == DBNull.Value ? 0 : Decimal.Parse(drAccount["AidAmount"].ToString());
                tempAccount.AidMonths = drAccount["AidMonths"] == DBNull.Value ? 0 : Int32.Parse(drAccount["AidMonths"].ToString());
                tempAccount.AidCode = drAccount["AidCode"].ToString();
                tempAccount.AidOther = drAccount["AidOther"].ToString();


            }
            catch (Exception exc)
            {

            }
            return tempAccount;
        }

        public static void SaveAccount(BusinessLogic.Account account)
        {

            try
            {
                command.CommandText = "usp_saveaccount";
                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("AccountId", account.AccountId));
                command.Parameters.Add(new SqlParameter("FirstName", account.FirstName));
                command.Parameters.Add(new SqlParameter("SecondName", account.SecondName));
                command.Parameters.Add(new SqlParameter("ThirdName", account.ThirdName));
                command.Parameters.Add(new SqlParameter("FourthName", account.FourthName));
                command.Parameters.Add(new SqlParameter("UNHCR", account.UNHCR));
                if (account.Governorate.Id == 0)
                    command.Parameters.Add(new SqlParameter("GenderId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("GenderId", account.Gender.Id));

                command.Parameters.Add(new SqlParameter("Telephone", account.Telephone));
                command.Parameters.Add(new SqlParameter("Email", account.Email));
                command.Parameters.Add(new SqlParameter("AddressInEgypt", account.AddressInEgypt));
                if (account.Governorate.Id == 0)
                    command.Parameters.Add(new SqlParameter("GovernorateId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("GovernorateId", account.Governorate.Id));

                if (account.Region.Id == 0)
                    command.Parameters.Add(new SqlParameter("RegionId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("RegionId", account.Region.Id));

                if (account.BirthDate == new DateTime())
                    command.Parameters.Add(new SqlParameter("BirthDate", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("BirthDate", account.BirthDate));
                command.Parameters.Add(new SqlParameter("BirthPlace", account.BirthPlace));

                if (account.MaritalStatus.Id == 0)
                    command.Parameters.Add(new SqlParameter("MaritalStatusId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("MaritalStatusId", account.MaritalStatus.Id));

                command.Parameters.Add(new SqlParameter("FamilyMembersCount", account.FamilyMembersCount));
                command.Parameters.Add(new SqlParameter("InfantsCount", account.InfantsCount));
                command.Parameters.Add(new SqlParameter("IsInEmergency", account.IsInEmergency));
                command.Parameters.Add(new SqlParameter("EmergencyDescription", account.EmergencyDescription));
                command.Parameters.Add(new SqlParameter("WasDetainedInEgypt", account.WasDetainedInEgypt));
                command.Parameters.Add(new SqlParameter("DetentionInEgyptDescription", account.DetentionInEgyptDescription));
                command.Parameters.Add(new SqlParameter("WasDetainedInHomeCountry", account.WasDetainedInHomeCountry));
                command.Parameters.Add(new SqlParameter("DetentionInHomeCountryDescription", account.DetentionInHomeCountryDescription));
                command.Parameters.Add(new SqlParameter("AverageMonthlyIncome", account.AverageMonthlyIncome));
                command.Parameters.Add(new SqlParameter("OtherIncomeSource", account.OtherIncomeSource));
                command.Parameters.Add(new SqlParameter("LeaseAmount", account.LeaseAmount));
                command.Parameters.Add(new SqlParameter("FoodAmount", account.FoodAmount));
                command.Parameters.Add(new SqlParameter("ElectricityAmount", account.ElectricityAmount));
                command.Parameters.Add(new SqlParameter("WaterAmount", account.WaterAmount));
                command.Parameters.Add(new SqlParameter("GasAmount", account.GasAmount));
                command.Parameters.Add(new SqlParameter("EducationAmount", account.EducationAmount));
                command.Parameters.Add(new SqlParameter("MedicineAmount", account.MedicineAmount));
                command.Parameters.Add(new SqlParameter("OtherAmount", account.OtherAmount));
                command.Parameters.Add(new SqlParameter("NumberOfRooms", account.NumberOfRooms));
                command.Parameters.Add(new SqlParameter("HasElectricity", account.HasElectricity));
                command.Parameters.Add(new SqlParameter("HasDrinkingWater", account.HasDrinkingWater));
                command.Parameters.Add(new SqlParameter("HasSanitation", account.HasSanitation));
                command.Parameters.Add(new SqlParameter("HasFurniture", account.HasFurniture));
                if (account.LivingStatus.Id == 0)
                    command.Parameters.Add(new SqlParameter("LivingStatusId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("LivingStatusId", account.LivingStatus.Id));

                command.Parameters.Add(new SqlParameter("JobDescriptionInHomeCountry", account.JobDescriptionInHomeCountry));
                command.Parameters.Add(new SqlParameter("TrainingCoursesWantedInEgypt", account.TrainingCoursesWantedInEgypt));
                command.Parameters.Add(new SqlParameter("JobWantedInEgypt", account.JobWantedInEgypt));
                if (account.ChronicDisease.Id == 0)
                    command.Parameters.Add(new SqlParameter("ChronicDiseaseId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("ChronicDiseaseId", account.ChronicDisease.Id));
                command.Parameters.Add(new SqlParameter("ChronicDiseaseOther", account.ChronicDiseaseOther));
                command.Parameters.Add(new SqlParameter("HasDisability", account.HasDisability));
                command.Parameters.Add(new SqlParameter("IsDisabilityMedicalReportAttached", account.IsDisabilityMedicalReportAttached));
                command.Parameters.Add(new SqlParameter("HasPsychiatricDisease", account.HasPsychiatricDisease));
                command.Parameters.Add(new SqlParameter("IsPsychiatricDiseaseMedicalReportAttached", account.IsPsychiatricDiseaseMedicalReportAttached));
                command.Parameters.Add(new SqlParameter("IsPregnant", account.IsPregnant));
                command.Parameters.Add(new SqlParameter("IsPregnantMedicalReportAttached", account.IsPregnantMedicalReportAttached));
                command.Parameters.Add(new SqlParameter("HasPregnancyProblems", account.HasPregnancyProblems));
                command.Parameters.Add(new SqlParameter("IsPregnancyProblemsMedicalReportAttached", account.IsPregnancyProblemsMedicalReportAttached));
                command.Parameters.Add(new SqlParameter("InterviewSummary", account.InterviewSummary));
                command.Parameters.Add(new SqlParameter("IsAction1", account.IsAction1));
                command.Parameters.Add(new SqlParameter("Action1Description", account.Action1Description));
                command.Parameters.Add(new SqlParameter("IsAction2", account.IsAction2));
                command.Parameters.Add(new SqlParameter("Action2Description", account.Action2Description));
                command.Parameters.Add(new SqlParameter("IsAction3", account.IsAction3));
                command.Parameters.Add(new SqlParameter("Action3Description", account.Action3Description));
                command.Parameters.Add(new SqlParameter("IsAction4", account.IsAction4));
                command.Parameters.Add(new SqlParameter("Action4Description", account.Action4Description));
                command.Parameters.Add(new SqlParameter("IsAction5", account.IsAction5));
                command.Parameters.Add(new SqlParameter("Action5Description", account.Action5Description));
                command.Parameters.Add(new SqlParameter("IsAction6", account.IsAction6));
                command.Parameters.Add(new SqlParameter("Action6Description", account.Action6Description));
                command.Parameters.Add(new SqlParameter("IsAction7", account.IsAction7));
                command.Parameters.Add(new SqlParameter("Action7Description", account.Action7Description));
                command.Parameters.Add(new SqlParameter("IsAction8", account.IsAction8));
                command.Parameters.Add(new SqlParameter("Action8Description", account.Action8Description));
                command.Parameters.Add(new SqlParameter("IsAction9", account.IsAction9));
                command.Parameters.Add(new SqlParameter("Action9Description", account.Action9Description));
                command.Parameters.Add(new SqlParameter("IsAction10", account.IsAction10));
                command.Parameters.Add(new SqlParameter("Action10Description", account.Action10Description));
                command.Parameters.Add(new SqlParameter("IsAction11", account.IsAction11));
                command.Parameters.Add(new SqlParameter("Action11Description", account.Action11Description));
                command.Parameters.Add(new SqlParameter("SocialWorkerDecisionCode", account.SocialWorkerDecisionCode));
                command.Parameters.Add(new SqlParameter("SocialWorkerDecisionDescription", account.SocialWorkerDecisionDescription));
                command.Parameters.Add(new SqlParameter("CommitteeDecision", account.CommitteeDecision));
                if (account.AidType.Id == 0)
                    command.Parameters.Add(new SqlParameter("AidTypeId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("AidTypeId", account.AidType.Id));
                command.Parameters.Add(new SqlParameter("AidAmount", account.AidAmount));
                command.Parameters.Add(new SqlParameter("AidMonths", account.AidMonths));
                command.Parameters.Add(new SqlParameter("AidCode", account.AidCode == null ? "":account.AidCode));
                command.Parameters.Add(new SqlParameter("AidOther", account.AidOther == null ? "" : account.AidOther));

                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception exc)
            {

            }
        }

        public static BusinessLogic.InitialLookups GetInitialLookupsForAccounts()
        {
            BusinessLogic.InitialLookups LookUps = new BusinessLogic.InitialLookups();

            try
            {
                command.CommandText = "usp_GetInitialLookupsForAccounts";
                command.Parameters.Clear();

                DataSet dsLookups = new DataSet();

                adapter.Fill(dsLookups);

                LookUps.Genders = ConvertTableToLookupsList(dsLookups.Tables[0]);
                LookUps.Governorates = ConvertTableToLookupsList(dsLookups.Tables[1]);
                LookUps.Regions = ConvertTableToLookupsList(dsLookups.Tables[2]);
                LookUps.MaritalStatuses = ConvertTableToLookupsList(dsLookups.Tables[3]);
                LookUps.LivingStatuses = ConvertTableToLookupsList(dsLookups.Tables[4]);
                LookUps.ChronicDiseases = ConvertTableToLookupsList(dsLookups.Tables[5]);
                LookUps.AidTypes = ConvertTableToLookupsList(dsLookups.Tables[6]);
                LookUps.SubAccountRelationship = ConvertTableToLookupsList(dsLookups.Tables[7]);
            }
            catch (Exception exc)
            {

            }
            return LookUps;
        }

        private static List<Item> ConvertTableToLookupsList(DataTable dtLookups)
        {
            List<Item> lookups = new List<Item>();

            try
            {
                for (int i = 0; i < dtLookups.Rows.Count; i++)
                {
                    lookups.Add(
                        new Item()
                        {
                            Id = Convert.ToInt32(dtLookups.Rows[i]["Id"]),
                            Value = dtLookups.Rows[i]["Value"].ToString()
                        }); ;
                }
            }
            catch (Exception exc)
            { }

            return lookups;
        }




       
    }
}
